package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Supplier;


public class DbSupplier implements IFDBSupplier {
	private Connection con;
	private DbLocation locationDB;
	
	/** Creates a new instance of DBSupplier */
	public DbSupplier() {
		con = DbConnection.getInstance().getDBcon();
		locationDB = new DbLocation();
	}

	//Implements the methods from the interface
	// get all suppliers
	@Override
	public ArrayList<Supplier> getAllSuppliers()
	{
		return miscWhere("");
	}
	
	@Override
	public ArrayList<Supplier> getAllSuppliers(String filter) {
		String wClause = " fName LIKE '%" + filter + "%'";
		return miscWhere(wClause);
	}
	
	//find the supplier from name
	@Override
	public Supplier searchSupplierByName(String name)
	{   
		String wClause = " fName = '" + name + "'";
		return singleWhere(wClause);
	}
	
	@Override
	public Supplier searchSupplierById(int id)
	{   
		String wClause = " p.id = '" + id + "'";
		return singleWhere(wClause);
	}

	//insert a new employee
	@Override
	public int insertSupplier(Supplier sup) throws Exception
	{  
		//call to get the next id number
		int nextID = GetMax.getMaxId("SELECT max(id) from People");
		nextID = nextID + 1;
		System.out.println("next id = " +  nextID);
		
		int rc = -1;
		
		String queryP="INSERT INTO People(id, fName, pAddress, phone, locationId)  VALUES('"+
				nextID  + "','"  +
				sup.getName()  + "','"  +
				sup.getAddress()  + "','"  +
				sup.getPhoneNO() + "','" +
				sup.getLocation().getId() + "')";

		String queryS="INSERT INTO Supplier(id, email)  VALUES('"+
				nextID  + "','"  +
				sup.getEmail()  + "')";

		System.out.println("insert : " + queryP + "\n" + queryS);
		try
		{ 
			// insert new supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(queryP);
			rc = stmt.executeUpdate(queryS);
			
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Supplier not created");
			throw new Exception ("Supplier is not inserted correct");
		}
		return(rc);
	}
	
	@Override
	public int updateSupplier(Supplier sup)
	{
		int rc=-1;

		String queryP="UPDATE People SET "+
				"fName='"+ sup.getName()+"', "+
				"pAddress='"+ sup.getAddress() + "', " +
				"locationId='"+ sup.getLocation().getId() + "', " +
				"phone='"+ sup.getPhoneNO() + "', " +
				"email='"+ sup.getEmail() + "' " +
				" WHERE id='"+ sup.getId() + "'";
		
		String queryS="UPDATE Supplier SET "+
				"email='"+ sup.getEmail() + "' " +
				" WHERE id='"+ sup.getId() + "'";
		
		System.out.println("Update query:" + queryP + "\n" + queryS);
		try
		{ 
			// update supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(queryP);
			rc = stmt.executeUpdate(queryS);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in supplier db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deleteSupplier(Supplier sup)
	{
		int rc=-1;

		String query="DELETE FROM People WHERE id = '" +
				sup.getId() + "'";
		System.out.println(query);
		try
		{ 
			// delete from supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex)
		{
			System.out.println("Delete exception in supplier db: "+ex);
		}
		return(rc);
	}

	//private methods
	//michWere is used whenever we want to select more than one supplier


	private ArrayList<Supplier> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();	

		String query =  buildQuery(wClause);

		try{ // read the supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				Supplier supObj = new Supplier();
				supObj = buildSupplier(results);	
				list.add(supObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	//Single where is used when we only select one supplier 	
	private Supplier singleWhere(String wClause)
	{
		ResultSet results;
		Supplier supObj = new Supplier();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				supObj = buildSupplier(results);
				stmt.close();
			}
			else{ //no supplier was found
				supObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return supObj;
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT p.id, fName, pAaddress, phone, locationId, email FROM People p, Supplier s";

		if (wClause.length() > 0)
			query = query + " WHERE "+ wClause + " AND s.id=p.id";
		else
			query = query + " WHERE s.id=p.id";
		
		return query;
	}
	
	//method to build an supplier object
	private Supplier buildSupplier(ResultSet results)
	{   
		Supplier supObj = new Supplier();
		try
		{ 
			// the columns from the table supplier are used
			supObj.setId(results.getInt("id"));
			supObj.setName(results.getString("fName"));
			supObj.setAddress(results.getString("pAaddress"));
			supObj.setLocation(locationDB.getLocationById(Integer.valueOf(results.getString("locationId"))));
			supObj.setPhoneNO(results.getString("phone"));
			supObj.setEmail(results.getString("email"));
		}
		catch(Exception e)
		{
			System.out.println("error in building the supplier object");
		}
		return supObj;
	}
}
